package dao.impl;

import bean.Student;
import dao.DBUtils;
import dao.StudentDao;
import util.StudentEnum;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {
    /**
     * 模糊查询总记录数
     * @param name
     * @param stuno
     * @param sex
     * @return
     */
    @Override
    public int total(String name, String stuno, int sex) {
            int total=0;
        try {
            List params=new ArrayList();
            StringBuffer sqlbuf=new StringBuffer(" select count(*) from student where 1=1 and state!=4 ");
            if(name!=null&&name.length()>0){
                sqlbuf.append(" and stuname like ? ");
                params.add("%"+name+"%");
            }
            if(stuno!=null&&stuno.length()>0){
                sqlbuf.append(" and stuno=? ");
                params.add(stuno);
            }
            if(sex!=-1){
                sqlbuf.append(" and sex=? ");
                params.add(sex);
            }
            resultSet = query(sqlbuf.toString(), params);
            while(resultSet.next()){
                total=resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return total;
    }

    /**
     * 分页+模糊查询
     * 分页:
     *      需要传参数 当前页数和每页记录数
     * 模糊查询:
     *      需要传参数 模糊查询的三个条件name stuno sex，不一定都有值,
     *      String类型的sql是固定长度的字符串，而模糊查询的查询条件不固定，
     *      这里需要动态的sql去查询，用户用了几个模糊查询条件，sql就通过StringBuffer动态追加几个条件
     * @param name
     * @param stuno
     * @param sex
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<Student> getStudents(String name,String stuno,int sex,int pageIndex,int pageSize) {
        List list= new ArrayList<Student>();
        List params=new ArrayList();
        //模糊查询部分
        try {
            StringBuffer sqlbuf=new StringBuffer(" select * from student where 1=1 and state!=4 ");
            if(name!=null&&name.length()>0){    //用户用了name条件进行模糊查询
                sqlbuf.append(" and stuname like ? ");  //追加sql的name条件，完善sql,注意空格
                params.add("%"+name+"%");   //占位符的值装入集合中
            }
            if(stuno!=null&&stuno.length()>0){ //用户用了stuno条件进行模糊查询
                sqlbuf.append(" and stuno=? "); //追加sql的stuno条件，完善sql
                params.add(stuno);
            }
            if(sex!=-1){
                sqlbuf.append(" and sex=? ");
                params.add(sex);
            }
            //分页查询部分
            sqlbuf.append(" limit ?,?");//limit 当前索引,每页记录数--》limit (pageIndex-1)*pageSize,pageSize;
            params.add((pageIndex-1)*pageSize);//占位符值装入集合
            params.add(pageSize);

            //query(sql,占位符值)开始查询
            resultSet = query(sqlbuf.toString(), params);
            while(resultSet.next()){
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setRegistered(resultSet.getString("registered"));
                student.setRegistered(resultSet.getString("address"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setState(resultSet.getInt("state"));
                student.setIntrodction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                System.out.println("分页查询结果:"+student);
                list.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }


    /**
     * 添加学员
     * @param student
     * @return
     */
    @Override
    public int insertStu(Student student) {
        int i =0;
        try {
            String sql="insert into student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params=new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);//枚举类，READING.type表示在读
            params.add(student.getIntrodction());
            params.add(student.getGid());
            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    /**
     * 通过id查询学员
     * @param sid
     * @return
     */
    @Override
    public Student findById(int sid) {
        Student student = new Student();
        try {
            String sql="select * from student where stuid=?";
            List params=new ArrayList();
            params.add(sid);

            resultSet=query(sql,params);
            while(resultSet.next()){
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setAddress(resultSet.getString("address"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setIntrodction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                student.setRegistered(resultSet.getString("registered"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
                student.setState(resultSet.getInt("state"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    /**
     * 更新学员
     * @param student
     * @return
     */
    @Override
    public int updateStu(Student student) {
        int update = 0;
        try {
            //这里需要大家自己完善非主键列以外的列的更新
            String sql="update student set stuname=?,stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,regdate=?,state=?,introduction=? where stuid=?";
            List params=new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(student.getRegDate());
            params.add(student.getState());
            params.add(student.getIntrodction());
            System.out.println("Dao中的stuid："+student.getStuId());
            params.add(student.getStuId());
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    /**
     * 假删除
     * 即当点击删除后修改学生的状态state值，数据库中该学员记录仍然保存，
     * 但是查询记录到页面展示的时候sql语句加上state!=4进行筛选
     * @param sid
     * @return
     */
    @Override
    public int delStu(String sid) {
        int update = 0;
        try {
            String sql="update student  set state=? where stuid=?";
            List params=new ArrayList();
            params.add(StudentEnum.DELETE.type);//枚举类，表示删除
            params.add(sid);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }
}
